package jsyf.oa.dao.device;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.dao.system.LogsDao;
import jsyf.oa.entity.device.DeviceDisplaceEntity;
import jsyf.oa.entity.device.DeviceTransportChangeEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by y on 2018/7/3.
 */
@Repository
public class DeviceDisplaceDao extends BaseDao {
    @Autowired
    LogsDao logsDao;

    public DeviceDisplaceDao() {
        super.iBaseEntity = new DeviceDisplaceEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, DeviceDisplaceEntity.class);
    }

    public JSONObject getAllCount(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "COUNT(1) AS allCount,\n" +
                "COUNT(STATE='提车在途' OR NULL) AS tcCount,\n" +
                "COUNT(STATE='库存' OR NULL) AS kcCount,\n" +
                "COUNT(STATE='交付在途' OR NULL) AS jfCount,\n" +
                "COUNT(STATE='已交' OR NULL) AS yjCount,\n" +
                "COUNT(STATE='退货在途' OR NULL) AS thCount,\n" +
                "COUNT(STATE='已退货' OR NULL) AS ythCount,\n" +
                "COUNT(STATE='移库在途' OR NULL) AS ykCount,\n" +
                "COUNT(STATE='已售' OR NULL) AS ysCount,\n" +
                "COUNT(STATE='未售' OR NULL) AS wsCount,\n" +
                "(select \n" +
                " count(1) \n" +
                "FROM\n" +
                "t_dvc_displace \n" +
                "WHERE 1=1\n" +
                "AND DEL_FLAG=0 AND TYPE=1\n" +
                "AND state is null\n" +
                " ORDER BY CREATE_TIME DESC) AS noCount\n" +
                "FROM\n" +
                "t_dvc_displace \n" +
                "WHERE 1=1" + "\n");
        sql.append(condition);
        JSONObject countList = super.queryForJsonObject(sql.toString(), params);
        return countList;
    }

    public List<JSONObject> findCompanyList(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "ID AS companyId,\n" +
                "`NAME` AS companyName,\n" +
                "All_NAME AS companyAllName\n" +
                "FROM\n" +
                "t_sys_company\n" +
                "WHERE 1=1\n");
        sql.append(condition);
        List<JSONObject> countList = super.queryForJsonList(sql.toString());
        return countList;
    }

    public List<JSONObject> findRegionByCompanyId(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "ID AS regionId,\n" +
                " `NAME` AS regionName,\n" +
                " PARENT_ID AS parentId,\n" +
                "COMPANY_ID AS companyId\n" +
                "FROM\n" +
                "t_sys_region\n" +
                "WHERE 1=1\n");
        sql.append(condition);
        List<JSONObject> regions = super.queryForJsonList(sql.toString());
        return regions;
    }


    public List<JSONObject> findBigRegionManagerByRegionId(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "v_sys_user.id AS userId,\n" +
                "v_sys_user.`name` AS userName,\n" +
                "v_sys_user.roleName AS roleName\n" +
                "FROM\n" +
                "v_sys_user\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=v_sys_user.id\n" +
                "WHERE 1=1\n");
        sql.append(condition);
        sql.append("GROUP BY v_sys_user.id");
        List<JSONObject> regions = super.queryForJsonList(sql.toString());
        return regions;
    }

    public List<JSONObject> findRegionManagerByBigRegionManagerId(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_sys_user.ID AS userId,\n" +
                "t_sys_user.`NAME` AS userName,\n" +
                "t_sys_role_name.`NAME` AS roleName\n" +
                "FROM\n" +
                " t_sys_user LEFT JOIN t_sys_user_2_region ON t_sys_user.ID = t_sys_user_2_region.USER_ID" +
                " LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID" +
                " LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID" +
                " LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "WHERE 1=1 AND t_sys_region.PARENT_ID = 0 AND t_sys_user.JOB_STATE != 1 AND t_sys_role_name.`NAME` = \"区域经理\"\n");
        sql.append(condition);
        List<JSONObject> regions = super.queryForJsonList(sql.toString());
        return regions;
    }

    public JSONObject getBigManagerAndRegion(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "id,\n" +
                "`name` ,\n" +
                "departName\n" +
                "FROM\n" +
                "v_sys_user\n" +
                "WHERE 1=1\n" +
                "AND roleName='大区经理'\n" +
                "AND jobState<>1\n" +
                "AND id=(\n" +
                "SELECT\n" +
                "PARENT_ID\n" +
                "FROM\n" +
                "t_sys_user\n" +
                "WHERE 1=1\n" +
                "AND t_sys_user.ID='"+userId+"'\n" +
                ")\n");
        JSONObject result = super.queryForJsonObject(sql.toString());
        return result;
    }


    /*查询出库附件
    * 司机证件-6
    * 设备照片-7
    * 板车照片-8
    * */
    public List<JSONObject> getCertificatesByOutId(String outId) {
        return super.queryForJsonList("SELECT * FROM v_com_attach WHERE 1=1 AND moduleId=" + " '" + outId + "'" + "AND attach_module=6", new Object[]{});

    }

    public List<JSONObject> getDevicePicturesByOutId(String outId) {
        return super.queryForJsonList("SELECT * FROM v_com_attach WHERE 1=1 AND moduleId=" + " '" + outId + "'" + "AND attach_module=7", new Object[]{});

    }

    public List<JSONObject> getDrayPicturesByOutId(String outId) {
        return super.queryForJsonList("SELECT * FROM v_com_attach WHERE 1=1 AND moduleId=" + " '" + outId + "'" + "AND attach_module=8", new Object[]{});

    }

    public JSONObject getImg(String moduleId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT group_concat(t_attachment.FILE_URL) as img");
        sql.append(" FROM");
        sql.append(" t_attach_module LEFT JOIN t_attachment ON t_attach_module.ATTACH_ID=t_attachment.ID");
        sql.append(" WHERE");
        sql.append(" MODULE_ID =? AND ATTACH_MODULE=4");
        return super.queryForJsonObject(sql.toString(), new Object[]{moduleId});
    }

    public List<JSONObject> getOldSample(String condition, String type) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT");
        sql.append(" t_dvc_displace.ID as id,\n" +
                "  t_sys_company.`NAME` as companyName,\n" +
                "  BRAND_NAME as brandName,\n" +
                "  STORE_ADDRESS as storeAddress," +
                "SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) as subStoreAddress ,\n");
        if ("库存地".equals(type)) {
            sql.append(" \n" +
                    "  CASE WHEN  STORE_ADDRESS=\"直发客户交机\" THEN 9999\n" +
                    "  WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"哈尔滨\" THEN 0\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"长春\" THEN 1\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"沈阳\" THEN 2\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"徐州\" THEN 3\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"常州\" THEN 4\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"合肥\" THEN 5\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"石家庄\" THEN 6\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"兰州\" THEN 7\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1)= \"银川\" THEN 8\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1)= \"长沙\" THEN 9" +
                    " ELSE \n" +
                    "CASE WHEN COMPANY_NAME=\"黑龙江灜源\" THEN 0\n" +
                    "     WHEN COMPANY_NAME=\"吉林清源\" THEN 1\n" +
                    "     WHEN COMPANY_NAME=\"辽宁灜湖\" THEN 2\n" +
                    "     WHEN COMPANY_NAME=\"江苏滚滚向前\" THEN 4\n" +
                    "WHEN COMPANY_NAME=\"安徽灜湖\" THEN 5\n" +
                    "WHEN COMPANY_NAME=\"河北灜源\" THEN 6\n" +
                    "WHEN COMPANY_NAME=\"甘肃&宁夏\" THEN 8\n" +
                    "WHEN COMPANY_NAME=\"湖南清源\" THEN 9 END\n" +
                    "END AS storeAddressNo,");
        }
        sql.append("  STATE as state,\n" +
                "  MODEL_NAME as modelName,\n" +
                "  SEQUENCE as sequence,\n" +
                "  FACTORY_DATE as factoryDate," +
                "  ESTIMATE_PRICE as estimatePrice,HOURS as hours," +
                "  DEVICE_PHOTO_NAME AS devicePhotoName,DEVICE_PHOTO_URL AS devicePhotoUrl," +
                "  \n" +
                "  REMARK as remark");
        sql.append(" FROM");
        sql.append(" t_dvc_displace LEFT JOIN t_sys_company ON t_dvc_displace.FK_COMPANY_ID=t_sys_company.ID");
        sql.append(" WHERE TYPE = 1 AND STATE NOT in(\"已交\",\"已退货\")\n" +
                "AND t_dvc_displace.DEL_FLAG = 0  AND t_dvc_displace.AUDIT_FLAG=1");
        if (!StringUtils.isBlank(condition)) {
            sql.append(condition);
        }
        if ("提车公司".equals(type)) {
            sql.append(" ORDER BY t_sys_company.SORT_NO,t_dvc_displace.STORE_ADDRESS desc,t_dvc_displace.CREATE_TIME DESC");
        } else if ("品牌".equals(type)) {
            sql.append(" ORDER BY t_dvc_displace.BRAND_NAME,t_sys_company.SORT_NO,t_dvc_displace.STORE_ADDRESS desc,t_dvc_displace.CREATE_TIME DESC");
        } else {
            sql.append(" ORDER BY storeAddressNo,t_sys_company.SORT_NO,t_dvc_displace.STORE_ADDRESS,t_dvc_displace.CREATE_TIME DESC");
        }
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getOldHouse(String condition, String type) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT");
        sql.append(" t_dvc_displace.ID as id,\n" +
                "  t_sys_company.`NAME` as companyName,\n" +
                "  HOUSE_NAME as houseName,\n" +
                "  HOUSE_ADDRESS as houseAddress,\n" +
                "  STATE as state,\n" +
                "  BUILDING_NUMBER as buildingNumber,\n" +
                "  AREA as area,TRANSFER as transfer,\n" +
                "  REMARK as remark");
        sql.append(" FROM t_dvc_displace LEFT JOIN t_sys_company ON t_dvc_displace.FK_COMPANY_ID=t_sys_company.ID");
        sql.append(" WHERE");
        sql.append(" TYPE = 3 AND STATE = \"未售\" AND t_dvc_displace.DEL_FLAG = 0 and t_dvc_displace.AUDIT_FLAG=1");
        if (!StringUtils.isBlank(condition)) {
            sql.append(condition);
        }
        if ("提车公司".equals(type)) {
            sql.append(" ORDER BY t_sys_company.SORT_NO,t_dvc_displace.HOUSE_ADDRESS desc,t_dvc_displace.HOUSE_NAME,t_dvc_displace.CREATE_TIME DESC");
        } else if ("品牌".equals(type)) {
            sql.append(" ORDER BY t_dvc_displace.HOUSE_NAME,t_sys_company.SORT_NO,t_dvc_displace.HOUSE_ADDRESS desc,t_dvc_displace.CREATE_TIME DESC");
        } else {
            sql.append(" ORDER BY t_dvc_displace.HOUSE_ADDRESS desc,t_sys_company.SORT_NO,t_dvc_displace.HOUSE_NAME,t_dvc_displace.CREATE_TIME DESC");
        }
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getExportList(String type) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tt_dvc_displace.COMPANY_NAME as companyName,\n" +
                "  t_dvc_displace.REGION_NAME as regionName,\n" +
                "  t_dvc_displace.BIGREGION_MANAGER_NAME as bigManagerName,\n" +
                "  t_dvc_displace.REGION_MANAGER_NAME as regionManagerName,\n" +
                "  t_dvc_displace.BRAND_NAME as brandName,\n" +
                "  t_dvc_displace.REPLACEMENT_TYPE as replaceMentType, \n" +
                "  t_dvc_displace.SEQUENCE as sequence,\n" +
                "  t_dvc_displace.FACTORY_DATE as factoryDate,\n" +
                "  CASE WHEN t_dvc_displace.HOURS=\"0\"\n" +
                "  THEN \"无\" ELSE t_dvc_displace.HOURS\n" +
                "  END as hours,\n" +
                "  t_dvc_displace.STATE as state,\n" +
                "  t_dvc_displace.STORE_ADDRESS as storeAddress,\n" +
                "  CASE WHEN t_dvc_displace.ESTIMATE_PRICE=\"0\"\n" +
                "  THEN \"无\" ELSE t_dvc_displace.ESTIMATE_PRICE\n" +
                "  END as estimatePrice,\n" +
                "  CASE WHEN t_dvc_displace.REPLACEMENT_PRICE=\"0\"\n" +
                "  THEN \"无\" ELSE t_dvc_displace.REPLACEMENT_PRICE\n" +
                "  END as replacementPrice,\n" +
                "  CASE WHEN t_dvc_displace.SELL_PRICE=\"0\"\n" +
                "  THEN \"无\" ELSE t_dvc_displace.SELL_PRICE\n" +
                "  END as sellPrice,\n" +
                "  CASE WHEN t_dvc_displace.REPLACEMENT_SUBSIDY=\"0\"\n" +
                "  THEN \"无\" ELSE t_dvc_displace.REPLACEMENT_SUBSIDY\n" +
                "  END as replacementSubsidy,\n" +
                "  CASE WHEN t_attachment.FILE_NAME IS NULL\n" +
                "  THEN \"无\" ELSE t_attachment.FILE_NAME\n" +
                "  END as fileName,\n" +
                "  t_dvc_displace.DEPARTURE as departure,\n" +
                "  t_dvc_displace.DESTINATION as destination,\n" +
                "  t_dvc_displace.TRANSPORT_COMPANY as transportCompany,\n" +
                "  CASE WHEN t_dvc_displace.FREIGHT=\"0\"\n" +
                "  THEN \"无\" ELSE t_dvc_displace.FREIGHT\n" +
                "  END as freight,\n" +
                "  CASE WHEN t_dvc_displace.DEDUCTION=\"0\"\n" +
                "  THEN \"无\" ELSE t_dvc_displace.DEDUCTION\n" +
                "  END as deduction,\n" +
                "  t_dvc_displace.DEDUCTION_INSTRUCTIONS as deductionInstructions,\n" +
                "  t_dvc_displace.MODEL_CAR_LICENSE as modelCarLicense,\n" +
                "  t_dvc_displace.DRIVER_NAME as driverName,\n" +
                "  t_dvc_displace.DRIVER_TEL as driverTel,\n" +
                "  CASE WHEN t_dvc_outbound.TYPE =\"0\"\n" +
                "  THEN \"出库\"\n" +
                "  WHEN t_dvc_outbound.TYPE =\"1\"\n" +
                "   THEN \"移库\"\n" +
                "  WHEN t_dvc_outbound.TYPE =\"2\"\n" +
                "   THEN \"退货\" \n" +
                "  ELSE \"无\" END AS outType,\n" +
                "  t_con_main.CONTRACT_NO as contractNo,\n" +
                "  t_dvc_outbound.PICK_UP_ADDRESS as pickUpAddress,\n" +
                "  t_dvc_outbound.DELIVERY_ADDRESS as delivery_address,\n" +
                "  t_dvc_outbound.TRANSPORT_COMPANY as outTransportCompany,\n" +
                "  CASE WHEN  t_dvc_outbound.SPECIAL_FEE=\"0.00\"\n" +
                "  THEN \"无\"\n" +
                "  ELSE t_dvc_outbound.SPECIAL_FEE END\n" +
                "  as outSpecialFee,\n" +
                "  CASE WHEN  t_dvc_outbound.DEDUCTION_FEE=\"0.00\"\n" +
                "  THEN \"无\"\n" +
                "  ELSE t_dvc_outbound.DEDUCTION_FEE END\n" +
                "  as outDeductionFee,\n" +
                "  t_dvc_outbound.DEDUCTION_REMARK as outDeductionRemark,\n" +
                "  t_dvc_outbound.DRAY_LICENSE as outDrayLicense,\n" +
                "  t_dvc_outbound.DRIVER_NAME as outDriverName,\n" +
                "  t_dvc_outbound.DRIVER_TEL as outDriverTel,\n" +
                "  t_dvc_displace.REMARK as remark,\n" +
                "  t_dvc_displace.MODEL_NAME as modelName,\n" +
                "  t_dvc_displace.KILOMETERS as kilometers,\n" +
                "  t_dvc_displace.TRANSFER as transfer,\n" +
                "  t_dvc_displace.INSURANCE_EXPIRATION_DATE as insuranceExpirationDate,\n" +
                "  t_dvc_displace.HOUSE_NAME as houseName,\n" +
                "  t_dvc_displace.BUILDING_NUMBER as buildingNumber,\n" +
                "  t_dvc_displace.HOUSE_ADDRESS as houseAddress,\n" +
                "  t_dvc_displace.AREA as area,\n" +
                "  t_dvc_displace.HOUSES_PROPERTY as housesProperty\n" +
                "FROM\n" +
                "\tt_dvc_displace\n" +
                "LEFT JOIN t_attach_module ON t_attach_module.MODULE_ID=t_dvc_displace.ID\n" +
                "LEFT JOIN t_attachment ON t_attach_module.ATTACH_ID=t_attachment.ID\n" +
                "LEFT JOIN t_dvc_outbound ON t_dvc_displace.ID=t_dvc_outbound.SAMPLE_ID\n" +
                "LEFT JOIN t_con_main ON t_con_main.ID=t_dvc_outbound.CONTRACT_ID\n" +
                "WHERE STATE is not null AND t_dvc_displace.DEL_FLAG=0");
        sql.append(" AND t_dvc_displace.TYPE=?");
        sql.append(" ORDER BY t_dvc_displace.STORE_ADDRESS desc,t_dvc_displace.MODEL_NAME,t_dvc_displace.CREATE_TIME DESC\n");
        return super.queryForJsonList(sql.toString(), new Object[]{type});
    }

    @Override
    public Integer update(IBaseEntity entity) {
        entity.setUpdateTime(super.getCurrentTime());
        logsDao.saveLogs(entity);
        return super.update(entity.getTableName(), entity.getJSONObject(), entity.getJSONObjectId());
    }

    @Override
    public Integer add(IBaseEntity entity) {
        entity.setId(super.generateKey());
        entity.setCreateTime(super.getCurrentTime());
        entity.setUpdateTime(super.getCurrentTime());
        logsDao.addLogs(entity);
        return super.insert(entity.getTableName(), entity.getJSONObject());
    }


    public Integer updateDisplaceAudit(String replacementId, Integer auditFlag, String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE t_dvc_displace\n" +
                "SET AUDIT_FLAG=" + auditFlag + "\n");
        sql.append(condition);
        sql.append("WHERE REPLACEMENT_ID='" + replacementId + "'");
        return this.getJdbcTemplate().update(sql.toString());
    }


}
